package gbench.sandbox.jdbc;

import org.junit.jupiter.api.Test;

import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;

import gbench.common.matlib.data.DataReader.DFrame;
import gbench.common.tree.LittleTree;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Jdbc;
import gbench.common.tree.LittleTree.Json;
import gbench.common.tree.LittleTree.Jdbc.JdbcConfig;
import gbench.common.tree.LittleTree.Jdbc.JdbcExecute;
import gbench.common.tree.LittleTree.Jdbc.JdbcQuery;
import gbench.common.tree.LittleTree.Jdbc.SqlPatternPreprocessor;
import gbench.commonApp.jdbc.IJdbcApp;
import gbench.commonApp.jdbc.ISqlDatabase;
import gbench.commonApp.jdbc.Neo4jApp;

import static gbench.common.matlib.MatlibCanvas.*;
import static gbench.common.matlib.data.DataReader.DFrame.*;
import static gbench.common.tree.LittleTree.*;
import static gbench.common.tree.LittleTree.IRecord.*;
import static gbench.common.tree.LittleTree.SimpleRecord.REC2;

/**
 * 通用数据库的查询演示
 * @author gbench
 *
 */
public class JunitDB3 {
    
    /**
     * mysql 数据库操作接口<br>
     * MysqlDatabase
     * @author gbench
     *
     */
    @JdbcConfig(url = "jdbc:mysql://localhost:3306/my1206?serverTimezone=GMT%2B8", user = "root", password = "123456")
    interface MysqlDatabase extends ISqlDatabase { // 数据接口
        @JdbcQuery
        List<IRecord> getUsers(String sex);
        @JdbcExecute
        void addUser(String name,String sex,int code);
        @JdbcQuery
        List<IRecord> groupBySex();
    }

    /**
     * mysql 的使用演示
     */
    @Test
    public void mysqldb1() {
        final var database = IJdbcApp.newNspebDBInstance("db3.sql",MysqlDatabase.class); // 创建数据库像
        final var proxy = database.getProxy(); // 提取代理对象
        final var jdbc = proxy.findOne(Jdbc.class); // 获取jdbc对象
        final var rb = IRecord.rb("#id,~32name,2 sex,code"); // 数据结构
        final var demo = IRecord.A(1, "张三", "李四",25); // 示例数据
        final var sqls = SQL.of("user", rb.build(demo)).createTable(true); // 创建表
        
        jdbc.withTransaction(sess -> {
            XVec.OF(sqls).delete(1).stream().peek(System.out::println).forEach(trycatch2(sess::sql2execute)); // 创建一个单表结构
            Stream.iterate(1, i -> i < 10, i -> i + 1)
                .map(i -> rb.get(i, "zhang" + i, i%2==0?"女":"男",i)).map(SQL.bd("user")::get).map(SQL::insert)
                .peek(System.out::println)
                .forEach(trycatch2(sess::sql2execute));
            println("\n描述数据表结构:\n",sess.sql2u("desc user",dfmclc));
            println("#addUser:\n",sess.sqlexecute("#addUser", REC("name","李四", "sex","男","code",1001)));
            println("\n查询用户信息:\n",sess.sql2records("select * from user").stream().collect(dfmclc));
            println("\n查询用户信息-女:\n",sess.psql2records("select * from user where sex=?",A("女")).stream().collect(dfmclc));
            println("\n查询用户信息-女:\n",sess.psql2u("select * from user where sex=?",A("女"),dfmclc));
            println("\n查询用户信息-女:\n",sess.psql2records("select * from user where sex=?",REC(1,"女")).stream().collect(dfmclc));
            println("\n查询用户信息-女:\n",sess.psql2u("select * from user where sex=?",REC(1,"女"),dfmclc));
            println("\n查询用户信息-女男:\n",sess.psql2records("select * from user where sex in (?,?)",REC(1,"女",2,"男")).stream().collect(dfmclc));
            println("\n查询用户信息-女男:\n",sess.psql2u("select * from user where sex in (?,?)",REC(1,"女",2,"男"),dfmclc));
            println("\n查询用户信息-女男:\n",sess.sql2records("select * from user where sex in (#man,#lady)",REC("male","男","female","女")).stream().collect(dfmclc));
            println("\n查询用户信息-女男:\n",sess.sql2u("select * from user where sex in (#man,#lady)",REC("male","男","female","女"),dfmclc));
            println("\n查询女性用户信息:\n",sess.sql2records("#getUsers", REC("sex","女")).stream().collect(dfmclc));
        }, Jdbc.M(SqlPatternPreprocessor.class,proxy.findOne(SqlPatternPreprocessor.class )) );
        println(database.getUsers("女").stream().collect(dfmclc).head(5));
        println("addUser");
        database.addUser("王五", "男",1002);
        println("查看用户数据:\n",jdbc.sql2records("select * from user").stream().collect(DFrame.dfmclc));
        println("人员统计:\n",database.groupBySex().stream().collect(DFrame.dfmclc));
    }
    
    @Test
    public void mysqldb2() {
        final var db = IJdbcApp.newNspebDBInstance("", MysqlDatabase.class);
        final var jdbc = db.getProxy().findOne(Jdbc.class);
        jdbc.withConnection(conn->{
            final var rs = conn.prepareStatement("select * from user").executeQuery(); rs.next(); // 移动到第一条数据
            println("readLine:\n", Jdbc.readline(rs));
            Jdbc.readlineS(rs).limit(2).forEach(e->println("--",e)); // 继续 读两行
            Jdbc.readlineS(rs).limit(2).forEach(e->println("**",e)); // 在读联行
            println("-- readlineS 3:\n", Jdbc.readlineS(rs, true).limit(3).collect(DFrame.dfmclc)); // 不会关闭 ，因为 短路
            println("** readlineS 3:\n", Jdbc.readlineS(rs,true).limit(Integer.MAX_VALUE).collect(DFrame.dfmclc)); // resultset 会关闭掉，因为 流被短路
            println("++ readlineS 3:\n", Jdbc.readlineS(rs, true).collect(DFrame.dfmclc)); // 会触发异常，因为rs已经关闭
            return null;
        }); 
    }
    
    @Test
    public void mysqldb3() {
        final var db = IJdbcApp.newNspebDBInstance("", MysqlDatabase.class);
        final var jdbc = db.getProxy().findOne(Jdbc.class);
        jdbc.withConnection(conn->{
            final var rs = conn.prepareStatement("select * from user").executeQuery();rs.next();
            println("readLine:\n", Jdbc.readline(rs));
            Jdbc.readlineS(rs).limit(2).forEach(e->println("--",e));
            Jdbc.readlineS(rs).limit(2).forEach(e->println("**",e));
            println("-- readlineS 3:\n", Jdbc.readlineS(rs, true).limit(3).collect(DFrame.dfmclc)); // resultset 不会会关闭掉，因为 短路
            return println("** readlineS 3:\n", Jdbc.readlineS(rs, true).collect(DFrame.dfmclc)); // resultset 会关闭掉
        });
    }
    
    /**
     * neo4j 数据库操作接口<br>
     * Neo4jDatabase
     * @author gbench
     *
     */
    @JdbcConfig(url = "jdbc:neo4j:bolt://localhost/mydb?nossl", driver = "org.neo4j.jdbc.Driver", user = "neo4j", password = "123456")
    interface Neo4jDatabase extends ISqlDatabase { // 数据接口
        @JdbcQuery
        List<IRecord> actIn(String name);
        @JdbcQuery
        List<IRecord> summary();
        @JdbcExecute
        void purchase(long transcode, String customer, String cashier, String product, String shop, double quantity,String unit,double price ,Date createtime);
    }
    
    /**
     * 创建 Neo4jApp 对象
     * @return Neo4jApp
     */
    public Neo4jApp neo4jApp(){
        final var ai = new AtomicInteger(0);
        return Neo4jApp.newInstance("",null,null)
            .set_intialize_handler(g->{// 把交易代码也写入到边明路径中。
                final var p = g.getEdge_name_renderer();// 记录老的边名记录器
                g.setEdge_name_renderer(edge->p.apply(edge)+"_"+edge.attr("transcode",ai.getAndIncrement()));// 在边实体记录中增加transcode
                if(true)g.setAttr_ops((attributeSets,rec)->{
                    final var key = rec.str("key"); // 边名/顶点名 
                    final var name = rec.str("name"); // 属性名 
                    final var value = rec.get("value", e -> (e instanceof IRecord)
                            ? ((IRecord) e).json().replace("\"", "\\\"")
                            : e != null && !e.getClass().isPrimitive() && !Number.class.isAssignableFrom(e.getClass())&& ! (e instanceof String)
                                ? LittleTree.trycatch(obj->Json.obj2json(obj).replace("\"", "\\\"")).apply(e)
                                : e);// 属性值
                    final var as = attributeSets.compute(key,(k,v)->(v==null?REC():v));
                    as.compute(name, v->{
                        if(v==null) {
                            return value;
                        }else {
                            final var ll = as.pathL(name);
                            if(!ll.contains(value))ll.add(value);
                            return ll.size()>1?ll:ll.get(0);
                        }
                    });
                });
            },false);// 注释intialize_handler 采用增量覆盖的方式 即replace 参数使用false
    }
    
    /**
     * neo4j 的使用演示
     */
    @Test
    public void neo4jdb() {
        System.setProperty("user.timezone","Asia/Shanghai"); // 设置时间格式
        final var database = IJdbcApp.newNspebDBInstance("db3.sql", Neo4jDatabase.class); // 创建数据库像
        final var proxy = database.getProxy(); // 提取代理对象
        final var jdbc = proxy.findOne(Jdbc.class); // 获取jdbc对象
        final var profileProto = REC();// 摘要信息
        final var purchaseProto = REC("0#vlblk", "Customer", "1#vlblk", "Product", "elblk", "Purchase", "createtime", CronTime.sdf.format(new Date()), 
            "shop", "开封府", "unit", "斤", "price", 6, "quantity", 3, "cashier", "包拯");// 买卖关系
        final var ai = new AtomicInteger(0);
        final var g = neo4jApp().graph(REC2( // 交易的事务记录
            // 关系信息
            "展昭/苹果", purchaseProto.derive("transcode", ai.getAndIncrement(), "price", 7.8)// 张三从王五那里买了3斤苹果
            , "张龙/苹果", purchaseProto.derive("transcode", ai.getAndIncrement() )// 张三从赵六那里买了3斤苹果
            , "赵虎/苹果", purchaseProto.derive("transcode", ai.getAndIncrement() )// 张三从陈七那里买了3斤苹果
            
            // 基础属性
            , "展昭", profileProto.derive("0#vlblk", "Customer", "$address", "上海"), 
            "张龙", profileProto.derive("0#vlblk", "Customer", "$address", "北京"), 
            "赵虎",profileProto.derive("0#vlblk", "Customer", "$address", "广州"), 
            "苹果",profileProto.derive("$vlblk", "Product", "$sourceArea", "陕西")
        ));
        println("图结构:\n",g);
        
        jdbc.withTransaction(sess -> {
            final var mySess = sess.monad(sess.sql2records("#actIn", R("name", "Top Gun"))).bind(DFrame::ofrows); // 获取一个新的会话对象&绑定状态数据
            println("提取绑定的状态数据:Top Gun\t\n" + mySess.getData());// 提取绑定的状态数据
            println("执行查询:The Devil's Advocate\t\n" + mySess.sql2u("#actIn", R("name", "The Devil's Advocate"), DFrame.dfmclc));
            sess.sqlexecute("match (a:Customer) detach delete a");
            sess.sqlexecute(MFT("create {0}", g));// 创建数据表
            sess.sqlexecute("#purchase", REC("transcode", System.nanoTime(), "customer", "高斯", "cashier", "欧拉",
                "product", "香蕉", "shop", "大明宫", "quantity",200,"unit", "kg","price",5.8,"createtime",new Date()));
            println("#purchase的sql语句:\n",sess.sqlparse("#purchase", REC("transcode", System.nanoTime(), "customer", "高斯", "cashier", "欧拉",
                    "product", "香蕉", "shop", "大明宫", "quantity",200,"unit", "kg","price",5.8,"createtime",new Date())));
            println("数据查询:\n",sess.sql2records("match (n:Customer {name:#name}) return n",REC("name","高斯")).stream().collect(dfmclc));
            println("数据查询:\n",sess.sql2u("match (n:Customer {name:#name}) return n",REC("name","高斯"),dfmclc));
            println("数据查询:\n",sess.sql2u("match (n:Customer {name:'高斯'}) return n",dfmclc));
            database.purchase(System.nanoTime(), "关羽", "嬴政", "草莓", "布达拉宫", 1000, "斤",10, new Date());
            println("查看销售情况:\n",sess.sql2u("match (a:Customer)-[e]->(b) return a,e,b", dfmclc));
            println("销量统计的sql语句:\n",sess.sqlparse("#summary",null));
            println("销量统计的sql语句:\n",sess.sqlparse("#summary",REC()));
            println("销量统计:\n",sess.sql2u("#summary", dfmclc));
            println("销量统计:\n",sess.sql2records(" #summary"));
            println("销量统计:\n",sess.sql2stream(" #summary").collect(dfmclc));
        }, Jdbc.M(SqlPatternPreprocessor.class, proxy.findOne(SqlPatternPreprocessor.class)));

        println("top Gun\n",database.actIn("Top Gun").stream().collect(DFrame.dfmclc));
        println("销量统计:\n",database.summary().stream().collect(DFrame.dfmclc));
    }

}
